﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

/// <summary>
/// InfoDao 的摘要说明
/// </summary>
public class InfoDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings
        ["MySQLConnectionString"].ConnectionString;
    /// <summary>
    /// 获取所有的记录
    /// </summary>
    /// <returns> 数据List集合</returns>
    public static List<Info> FindAll()
    {
        List<Info> data = new List<Info>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "SELECT * from tb_info";

            // 构建了一个sql语句的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);   // 执行sql语句
            // 获取数据库访问的数据
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Info info = new Info();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.Title = Convert.ToString(rdr["title"]);
                info.Content = Convert.ToString(rdr["content"]);
                info.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                info.Author = Convert.ToString(rdr["author"]);
                info.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                info.CategoryMark = Convert.ToString(rdr["category_mark"]);
                data.Add(info);
            }
            rdr.Close();

        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========FindAll方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========FindAll方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }


        return data;
    }
    /// <summary>
    /// 获取info表中的所有记录
    /// </summary>
    /// <param name="start">起始位置</param>
    /// <param name="num">获取的记录数</param>
    /// <returns>数据List集合</returns>
    public static List<Info> FindAll(int start, int num)
    {
        List<Info> data = new List<Info>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();

            //SQL语句拼接形式
            string sql = "SELECT * FROM tb_info LIMIT " + start + "," + num;

            // 构建了一个sql语句的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);   // 执行sql语句
            // 获取数据库访问的数据
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Info info = new Info();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.Title = Convert.ToString(rdr["title"]);
                info.Content = Convert.ToString(rdr["content"]);
                info.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                info.Author = Convert.ToString(rdr["author"]);
                info.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                info.CategoryMark = Convert.ToString(rdr["category_mark"]);
                data.Add(info);
            }
            rdr.Close();

        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========FindAll方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========FindAll方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }


        return data;
    }
    /// <summary>
    /// 按照id获取记录
    /// </summary>
    /// <param name="id">信息的内部ID序号</param>
    /// <returns>指定的信息记录</returns>
    public static Info Find(long id)
    {
        Info data = new Info();

        MySqlConnection conn = new MySqlConnection(connStr);


        try
        {
            conn.Open();
            // SQL语句采用占位符，更加安全，可在一定程度上防止SQL注入
            string sql = "SELECT * FROM tb_info WHERE id=@id ";

            // 构建了一个sql语句的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);   // 执行sql语句
            // 获取对应的参数

            cmd.Parameters.AddWithValue("@id", id);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {

                data.Id = Convert.ToInt64(rdr["id"]);
                data.Title = Convert.ToString(rdr["title"]);
                data.Content = Convert.ToString(rdr["content"]);
                data.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                data.Author = Convert.ToString(rdr["author"]);
                data.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                data.CategoryMark = Convert.ToString(rdr["category_mark"]);

            }
            rdr.Close();

        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========Find方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========Find方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }


        return data;
    }
    /// <summary>
    /// 更新一条记录
    /// </summary>
    /// <param name="data"></param>
    public static void Update(Info data)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "UPDATE tb_info SET "
                + "title=@title,content=@content, author=@author "
                + "WHERE id=@id ";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@title", data.Title);
            cmd.Parameters.AddWithValue("@content", data.Content);
            cmd.Parameters.AddWithValue("@author", data.Author);
            cmd.Parameters.AddWithValue("@id", data.Id);
            // 无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========Update方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========Update方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    /// <summary>
    /// 删除一条记录
    /// </summary>
    /// <param name="id"></param>
    public static void Delete(long id)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "DELETE FROM tb_info WHERE id=@id "
                ;
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            
            cmd.Parameters.AddWithValue("@id", id);
            // 无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========Delete方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========Delete方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    /// <summary>
    /// 插入一条记录
    /// </summary>
    /// <param name="data">待插入的记录</param>
    /// <returns>插入记录的id</returns>
    public static long Insert(Info data)
    {
        long insertId = 0;
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "INSERT INTO tb_info "
                + "(title, content, publish_time, author, click_number, category_mark) "
                + "VALUES "
                + "(@title, @content, @publish_time, @author, @click_number, @category_mark) ";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@title", data.Title);
            cmd.Parameters.AddWithValue("@content", data.Content);
            cmd.Parameters.AddWithValue("@publish_time", data.PublishTime);
            cmd.Parameters.AddWithValue("@author", data.Author);
            cmd.Parameters.AddWithValue("@click_number", data.ClickNumber);
            cmd.Parameters.AddWithValue("@category_mark", data.CategoryMark);
            // 无返回结果集
            cmd.ExecuteNonQuery();
            // 返回插入的记录的数据库ID
            insertId = cmd.LastInsertedId;

        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("========Insert方法异常========");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("========Insert方法异常========");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return insertId;
    }
}
